﻿/****** Object:  Table [dbo].[Users]    Script Date: 10/13/2007 22:53:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
	[UserID] [uniqueidentifier] NOT NULL,
	[Username] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Password] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastLogin] [datetime] NOT NULL,
	[DateCreated] [datetime] NOT NULL,
	[Email] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecurityQuestion] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SecurityAnswer] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IsValid] [bit] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object:  Table [dbo].[Roles]    Script Date: 10/13/2007 22:53:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
	[RoleID] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object:  Table [dbo].[UsersInRoles]    Script Date: 10/13/2007 22:53:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UsersInRoles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RoleID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_UsersInRoles] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object:  StoredProcedure [dbo].[UserInRole]    Script Date: 10/13/2007 22:53:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserInRole]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.UserInRole
	(
	@UserName nvarchar(max), 
	@RoleName nvarchar(max),
	@isInRole bit OUTPUT
	)
AS
	/* SET NOCOUNT ON */

	
	IF (EXISTS( 
		SELECT * FROM UsersInRoles INNER JOIN
			Users ON UsersInRoles.UserID = Users.UserID INNER JOIN
			Roles ON UsersInRoles.RoleID = Roles.RoleID
		WHERE        
			(Roles.RoleName = @RoleName) 
			AND 
			(Users.Username = @UserName)
	))
        Set @isInRole = 1
    ELSE
        Set @isInRole = 0
	
	
	RETURN 
' 
END
/****** Object:  StoredProcedure [dbo].[DeleteUserFromRole]    Script Date: 10/13/2007 22:53:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteUserFromRole]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.DeleteUserFromRole
	(
	@username nvarchar(max),
	@roleName nvarchar(max)
	)
AS
	/* SET NOCOUNT ON */
	DELETE FROM UsersInRoles
FROM            UsersInRoles INNER JOIN
                         Users ON UsersInRoles.UserID = Users.UserID INNER JOIN
                         Roles ON UsersInRoles.RoleID = Roles.RoleID
WHERE        (Users.Username = @username) AND (Roles.RoleName = @roleName)
	RETURN
' 
END
/****** Object:  StoredProcedure [dbo].[AddUserToRole]    Script Date: 10/13/2007 22:53:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddUserToRole]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.AddUserToRole
	(
	@username nvarchar(max),
	@roleName nvarchar(max)
	)
AS
	/* SET NOCOUNT ON */
	
	Declare @roleID uniqueidentifier
	Declare @userID uniqueidentifier
	
	SELECT        @roleID = RoleID
FROM            Roles
WHERE        (RoleName = @roleName)


	SELECT        @userID = UserID
FROM            Users
WHERE        (Username = @username)
	
	INSERT INTO UsersInRoles
                         (RoleID, UserID)
VALUES        (@roleID,@userID)
	RETURN
' 
END
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UsersInRoles_Roles]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))
ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_UsersInRoles_Roles] FOREIGN KEY([RoleID])
REFERENCES [Roles] ([RoleID])
ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [FK_UsersInRoles_Roles]
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UsersInRoles_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UsersInRoles]'))
ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_UsersInRoles_Users] FOREIGN KEY([UserID])
REFERENCES [Users] ([UserID])
ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [FK_UsersInRoles_Users]